const db = require("../db");
const db_execute = async (sql, params) => {
    if (!params) {
        return db.execute(sql, params); // 没有参数直接执行
    } else {
        const new_params = params.map(item => {
            if (typeof item === 'number') {
                return item.toString();  // 如果是数字，转换成字符串
            }
            return item;
        });
        return db.execute(sql, new_params); // 执行查询并传递转换后的参数
    }
};

class Payroll {
    static async getPayroll(currentPage = 1, pageSize = 10, searchMsg) {
        try {
            const limit = (currentPage - 1) * pageSize
            let searchWord = []
            let queryParams = []
            if (searchMsg.employee_id) {
                searchWord.push(`employee_id = ?`)
                queryParams.push(Number(searchMsg.employee_id))
            }
            if (searchMsg.salary_date) {
                searchWord.push(`salary_date BETWEEN ? AND ?`)
                queryParams.push(searchMsg.salary_date[0], searchMsg.salary_date[1])
            }

            queryParams.push(pageSize, limit)
            const queryWhere = searchWord.length > 0 ? " WHERE " + searchWord.join(" AND ") : " "
            const query = `SELECT * FROM employee_salary ${queryWhere} LIMIT ? OFFSET ?`
            const [rows] = await db_execute(query, queryParams)
            for (const row of rows) {
                const employee_id = row.employee_id;
                const [employee] = await db_execute(`SELECT name FROM employees WHERE employee_id = ?`, [employee_id])
                row.employee_name = employee[0].name
            }
            queryParams = queryParams.slice(0, queryParams.length - 2)
            const queryCount = `SELECT COUNT(*) as total FROM employee_salary ${queryWhere}`
            const [count] = await db_execute(queryCount, queryParams)
            return { data: rows, total: count[0].total }

        } catch (error) {
            throw new Error('获取薪资记录失败: ' + error.message)

        }
    }

    static async deletePayroll(id) {
        try {
            const query = `DELETE FROM employee_salary WHERE salary_id = ?`
            const result = await db_execute(query, [id])
            return result[0].affectedRows
        } catch (error) {
            throw new Error('删除薪资记录失败: ' + error.message)
        }
    }
}

module.exports = Payroll